-- EXEC SP_CREATE_UPDATE_ASSEMBLY_LOT 'LDS506BAB00A-50T10','TC6800680-01','MMW6W',null,'3684',null,null,null,null,749,null,'2006/08/22','Rewind',null,'ChipBond','Hold',null,null,null

ALTER PROC SP_CREATE_UPDATE_ASSEMBLY_LOT (
 
 @PART_NUMBER		VARCHAR(50), 
 @LOT_NUMBER 		VARCHAR(50),
 @PACKAGE_TYPE 		VARCHAR(50),
 @LEAD_COUNT 		VARCHAR(50),	
 @DATE_IN 		DATETIME,
 @DATE_OUT 		DATETIME,	
 @CUSTOMER_PO   	VARCHAR(50), 
 @QTY_IN 		INT,
 @QTY_OUT 		INT,
 @EXPECTED_DATE_OUT	DATETIME,
 @STAGENAME   		VARCHAR(50),
 @VENDOR_NAME 		VARCHAR(50),
 @LOT_STATUS 		VARCHAR(50),
 @HOLD_CODE 		VARCHAR(50),
 @HOLD_REASON 		VARCHAR(50),
 @Fab_Lot_No		varchar(50),
 @Lot_Category		varchar(10),
 @VEndor_Lot_NUMBER VARCHAR(50),--Release Number Cosidering As Vendor Lot Number
 @Date_CODE			VARCHAR(50) 
 )
 AS
 BEGIN
	
 	DECLARE @PROCESS_STEP_ID INT
 	DECLARE @LOCATION_ID INT
 	DECLARE @LOT_ID INT
	DECLARE @LOT_DATEIN DATETIME
	--setting lot category is production, when category is null
	if @lot_Category is null
	begin
		set @lot_Category = 'PROD'
	end
	 	
 	SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT,PROCESS_STEP WHERE PRODUCT.PART_NUMBER = @PART_NUMBER  AND PS_TYPE_NAME = 'Assembly' AND PS_TYPE = 'Assembly' AND PRODUCT.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
	SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE L_TYPE = 'Assembly' AND L_VENDOR_NAME = @VENDOR_NAME
	
	IF @PROCESS_STEP_ID IS NULL
 	BEGIN
		SELECT @PROCESS_STEP_ID = PROCESS_STEP.PROCESSSTEP_ID FROM PRODUCT_MAP_TABLE,PROCESS_STEP WHERE PRODUCT_MAP_TABLE.PART_NUMBER = @PART_NUMBER AND PS_TYPE_NAME = 'Assembly' AND PS_TYPE = 'Assembly' AND PRODUCT_MAP_TABLE.PROCESSSTEP_ID=PROCESS_STEP.PROCESSSTEP_ID 
 		IF @PROCESS_STEP_ID IS NULL 
 		BEGIN
			--For Discrepancy Lot Notification Alert
			IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = 'Assembly' AND IS_USER_NOTIFIED = 1 ) = 0 
			BEGIN
				INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status,TimeStamp )
					VALUES ( @part_number, @lot_number, @Fab_Lot_No, @vendor_name, 'Assembly','Discrepancy Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )
			END
				
			 -- DUMMY UPDATE TO AVOID SQLEXCEPTION
 			UPDATE LOT_TABLE SET LOT_NUMBER = 'LOT_NUMBER' WHERE LOT_NUMBER = 'LOT_NUMBER'

 			RETURN
 		END
	END
	
 
 	SELECT @LOT_ID = LOT_ID, @LOT_DATEIN = DATE_IN FROM LOT_TABLE WHERE PROCESSSTEP_ID = @PROCESS_STEP_ID AND LOCATION_ID = @LOCATION_ID AND LOT_NUMBER = @LOT_NUMBER
	
	IF @DATE_IN IS NULL AND @LOT_DATEIN IS NULL
    BEGIN
        SET @DATE_IN = CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101)
    END
    ELSE
    BEGIN
		IF @DATE_IN IS NULL AND @LOT_DATEIN IS NOT NULL
		BEGIn
			SET @DATE_IN = @LOT_DATEIN 
		END
    END

 	IF @LOT_ID IS NULL -- Check if the lot exists
 	BEGIN 	
		-- Create Lot
 		INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, Wafer_Qty_In, QTY_IN, DATE_IN, DATE_OUT, STAGE_NAME, LOT_CURRENT_STATUS, TIMESTAMP, LOT_ENTERED_BY, LOT_CATEGORY, LOT_DROP_OUT, PO_NUMBER, LOT_DATE_CODE) 
				VALUES (@PROCESS_STEP_ID, @LOCATION_ID, @LOT_NUMBER, @Fab_Lot_No, ISNUll(@VEndor_Lot_NUMBER, @LOT_NUMBER), null, @QTY_IN, @DATE_IN, @DATE_OUT, @STAGENAME, @LOT_STATUS, CURRENT_TIMESTAMP,1, @Lot_Category, 0, @CUSTOMER_PO, @DATE_CODE )
		SELECT @LOT_ID = @@IDENTITY

		-- For New Lot Notification Alert
		INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Wafer_Qty, Die_Qty, Wafer_id, Status, TimeStamp )
				VALUES ( @part_number, @lot_number, @Fab_Lot_No, @vendor_name, 'Assembly','New Lot Notification', null, @Qty_In, null, 'Open', CURRENT_TIMESTAMP )		
				
		INSERT INTO ASSEMBLY_LOT (LOT_ID, EXPECTED_DATE_OUT, Package_Type, Lead_Count, Hold_Code, Hold_Reason, TIMESTAMP)
			 VALUES (@LOT_ID, @EXPECTED_DATE_OUT, @PACKAGE_TYPE, @LEAD_COUNT, @Hold_Code, @Hold_Reason, CURRENT_TIMESTAMP )

	END
	ELSE
	BEGIN
		UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = @LOT_STATUS, QTY_IN = @QTY_IN, STAGE_NAME = @STAGENAME, Lot_Category = @Lot_Category, Lot_Drop_Out = 0, FAB_LOT_NUMBER = @Fab_Lot_No, VENDOR_LOT_NUMBER = ISNUll(@VEndor_Lot_NUMBER, @LOT_NUMBER), LOT_DATE_CODE = @DATE_CODE WHERE LOT_ID = @LOT_ID
		
		
		UPDATE ASSEMBLY_LOT SET EXPECTED_DATE_OUT = @EXPECTED_DATE_OUT, PACKAGE_TYPE = @PACKAGE_TYPE, LEAD_COUNT = @LEAD_COUNT, HOLD_CODE = @HOLD_CODE, HOLD_REASON = @HOLD_REASON, TIMESTAMP = CURRENT_TIMESTAMP WHERE LOT_ID = @LOT_ID
	END

	/* For Closing the Assembly Release Lots */
	IF ( (SELECT COUNT(DISTINCT SHIPPED_Lot_Number) FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID and STATUS = 'APPROVED' 
			AND SHIPPED_Lot_Number = @Fab_Lot_No ) > 0  )
	BEGIN
			UPDATE ASSM_RELEASE_TABLE SET STATUS = 'Closed' WHERE ASSEMBLY_RELEASE_ID = ( SELECT DISTINCT AR.ASSEMBLY_RELEASE_ID FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID and STATUS = 'APPROVED' 
			AND SHIPPED_Lot_Number = @Fab_Lot_No )
	END

	IF ( (SELECT COUNT(DISTINCT SHIPPED_Lot_Number) FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID and STATUS = 'APPROVED' 
			AND SHIPPED_Lot_Number = @LOT_NUMBER ) > 0  )
	BEGIN
			UPDATE ASSM_RELEASE_TABLE SET STATUS = 'Closed' WHERE ASSEMBLY_RELEASE_ID = ( SELECT DISTINCT AR.ASSEMBLY_RELEASE_ID FROM ASSM_RELEASE_LOT_TABLE ARL,ASSM_RELEASE_TABLE AR WHERE ARL.ASSEMBLY_RELEASE_ID = AR.ASSEMBLY_RELEASE_ID and STATUS = 'APPROVED' 
			AND SHIPPED_Lot_Number = @LOT_NUMBER )
	END
	
	/* END OF CLOSING ASSEMBLY RELESE LOTS */

	/*Closing the Previous Transit Lots when ever Lots are came in to Next Process*/
	EXEC SP_CLOSE_PREVIOUSPROCESS_AND_TRANSITLOTS @LOT_NUMBER, @FAB_LOT_NO, @QTY_IN, @DATE_IN

END